import java.sql.*;

public class StudentDatabaseApp {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/stu";
    private static final String USER = "your_username";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;

        try {
            // 1. 连接到数据库
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // 2. 创建表（如果尚未存在）
            String sqlCreateTable = "CREATE TABLE IF NOT EXISTS student (" +
                    "id INT AUTO_INCREMENT PRIMARY KEY," +
                    "name VARCHAR(50) NOT NULL," +
                    "origin VARCHAR(100)," +
                    "age INT," +
                    "grade VARCHAR(10)" +
                    ")";
            stmt = conn.createStatement();
            stmt.executeUpdate(sqlCreateTable);

            // 3. 插入记录（示例）
            String sqlInsert = "INSERT INTO student (name, origin, age, grade) VALUES (?, ?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(sqlInsert);
            pstmt.setString(1, "张三");
            pstmt.setString(2, "北京");
            pstmt.setInt(3, 20);
            pstmt.setString(4, "大一");
            pstmt.executeUpdate();

            // 4. 查询记录（示例）
            String sqlQuery = "SELECT * FROM student";
            ResultSet rs = stmt.executeQuery(sqlQuery);
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") +
                        ", Name: " + rs.getString("name") +
                        ", Origin: " + rs.getString("origin") +
                        ", Age: " + rs.getInt("age") +
                        ", Grade: " + rs.getString("grade"));
            }

            // 5. 更新记录（示例）
            String sqlUpdate = "UPDATE student SET age = ? WHERE id = ?";
            pstmt = conn.prepareStatement(sqlUpdate);
            pstmt.setInt(1, 21);
            pstmt.setInt(2, 1); // 假设我们要更新ID为1的学生的年龄
            pstmt.executeUpdate();

            // 6. 删除记录（示例）
            String sqlDelete = "DELETE FROM student WHERE id = ?";
            pstmt = conn.prepareStatement(sqlDelete);
            pstmt.setInt(1, 2); // 假设我们要删除ID为2的学生
            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 7. 关闭资源
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}